package cn.shujuhai.cms.mapper;


import cn.shujuhai.cms.domain.InfoArticle;
import com.github.yulichang.base.MPJBaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @Author: zhangDeZhong
 * @Date: 2024/12/5 12:01
 * @Description: 针对表【info_article(文章表)】的数据库操作Mapper
 **/
@Mapper
public interface InfoArticleMapper extends MPJBaseMapper<InfoArticle> {


    @Select("WITH InfoList AS (\n" +
            "    SELECT DISTINCT ic.article_id\n" +
            "    FROM info_channel ic\n" +
            "    WHERE ic.channel_id = #{channelId} and ic.deleted=0\n" +
            "),\n" +
            "ChannelList AS (\n" +
            "    SELECT ic.article_id, c.channel_id AS channel_id, c.channel_name AS channel_name\n" +
            "    FROM info_channel ic\n" +
            "    JOIN sys_channel c ON ic.channel_id = c.channel_id\n" +
            "    WHERE  ic.deleted=0 and ic.article_id IN (SELECT article_id FROM InfoList)\n" +
            ")\n" +
            "SELECT i.*,sc.channel_name as channelName, \n" +
            "       GROUP_CONCAT(DISTINCT cl.channel_id ORDER BY cl.channel_id) AS channels, \n" +
            "       GROUP_CONCAT(DISTINCT cl.channel_name ORDER BY cl.channel_name) AS channelNames\n" +
            "FROM info_article i\n" +
            "LEFT JOIN ChannelList cl ON i.article_id = cl.article_id\n" +
            "LEFT JOIN sys_channel sc ON sc.channel_id = i.channel_id\n" +
            "WHERE i.article_id IN (SELECT article_id FROM InfoList)\n" +
            " AND i.title LIKE CONCAT('%', #{title}, '%') \n" +
            "GROUP BY i.article_id, i.title\n" +
            "ORDER BY i.article_id\n")
    List<InfoArticle> getList(@Param("channelId") Long channelId, @Param("title") String title);

    @Select("WITH InfoList AS (\n" +
            "    SELECT DISTINCT ic.article_id\n" +
            "    FROM info_channel ic\n" +
            "    WHERE  ic.deleted=0\n" +
            "),\n" +
            "ChannelList AS (\n" +
            "    SELECT ic.article_id, c.channel_id AS channel_id, c.channel_name AS channel_name,c.url_key as url_key\n" +
            "    FROM info_channel ic\n" +
            "    JOIN sys_channel c ON ic.channel_id = c.channel_id\n" +
            "    WHERE  ic.deleted=0 and ic.article_id IN (SELECT article_id FROM InfoList)\n" +
            ")\n" +
            "SELECT i.*,sc.channel_name as channelName,sc.url_key as urlKey, \n" +
            "       GROUP_CONCAT(DISTINCT cl.channel_id ORDER BY cl.channel_id) AS channels, \n" +
            "       GROUP_CONCAT(DISTINCT cl.channel_name ORDER BY cl.channel_name) AS channelNames\n" +
            "FROM info_article i\n" +
            "LEFT JOIN ChannelList cl ON i.article_id = cl.article_id\n" +
            "LEFT JOIN sys_channel sc ON sc.channel_id = i.channel_id\n" +
            "WHERE i.article_id IN (SELECT article_id FROM InfoList)\n" +
            " AND MATCH(i.title, i.description) AGAINST(#{search}) \n" +
            "GROUP BY i.article_id, i.title\n" +
            "ORDER BY i.article_id\n")
    List<InfoArticle> search_list(String search);

}




